﻿Option Strict On
Option Explicit On
Public Class clsDBLotBadData
    ' szOrderBy:  0:ASC 1:DESC
    Public Function GetLotSizeData(ByVal szEquipID As String, ByVal szLot As String, ByVal szPreProcLotType As String, Optional ByVal szOrderBy As String = "0") As DataTable
        Dim szSql As String = ""

        '>> 2018/01/13 安部 GroupKey対応
        'szSql &= "Select LD.LOT_NO LOT_NO, JK.KOMOKUDATA LOT_SIZE , COALESCE(LBD.BAD_NUM, 0) LOT_BAD_CNT "
        'szSql &= "FROM  "
        'szSql &= "   PER.L_TAG_LOTDATA LD "
        'szSql &= "   INNER JOIN PER.L_JYOKENDATA JK ON (JK.KLOTNO = LD.LOT_NO AND KOTEI = 'LOTINFO' AND KOMOKUNAME='LOT SIZE') "
        'szSql &= "   LEFT OUTER JOIN (SELECT LOT_NO, SUM(CAT_CNT) BAD_NUM FROM PER.L_LOT_BAD_DATA  "
        'szSql &= "      WHERE EQUIP_ID = ? AND WAFER_LOTNO = ? "
        'szSql &= "      GROUP BY LOT_NO ) LBD ON (LBD.LOT_NO = LD.LOT_NO) "
        'szSql &= "WHERE WAFER_LOTNO = ? ORDER BY LOT_NO "
        If szPreProcLotType = "0" Then
            szSql &= "Select LD.LOT_NO LOT_NO, LD.COMPNUM LOT_SIZE , COALESCE(LBD.BAD_NUM, 0) LOT_BAD_CNT "
            szSql &= "FROM "
            szSql &= "   PER.L_TAG_LOTDATA LD "
            szSql &= "   LEFT OUTER JOIN (SELECT LOT_NO, SUM(CAT_CNT) BAD_NUM "
            szSql &= "                    FROM PER.L_LOT_BAD_DATA "
            szSql &= "                    WHERE EQUIP_ID = ? AND WAFER_LOTNO = ? "
            szSql &= "                    GROUP BY LOT_NO ) LBD ON (LBD.LOT_NO = LD.LOT_NO) "
            szSql &= "WHERE WAFER_LOTNO = ? "
            szSql &= " and invnum <> 0" '小Lot统合 2021/8/25 miaojf
        Else
            szSql &= "Select LD.LOT_NO LOT_NO, LD.COMPNUM LOT_SIZE , COALESCE(LBD.BAD_NUM, 0) LOT_BAD_CNT "
            szSql &= "FROM "
            szSql &= "   PER.L_TAG_LOTDATA LD "
            szSql &= "   LEFT OUTER JOIN (SELECT LOT_NO, SUM(CAT_CNT) BAD_NUM "
            szSql &= "                    FROM PER.L_LOT_BAD_DATA "
            szSql &= "                    WHERE EQUIP_ID = ? AND LOT_NO = ? "
            szSql &= "                    GROUP BY LOT_NO ) LBD ON (LBD.LOT_NO = LD.LOT_NO) "
            szSql &= "WHERE LD.LOT_NO = ? "

        End If
        '<< 2018/01/13 安部

        If szOrderBy = "1" Then
            szSql &= "AND LD.COMPNUM > 0 "
        End If
        szSql &= "ORDER BY LOT_NO "
        If szOrderBy = "1" Then
            szSql &= "DESC "
        End If

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szEquipID, szLot, szLot)
        If IsNothing(dt) Then
            GetLotSizeData = New DataTable
        Else
            GetLotSizeData = dt
        End If
    End Function

    Public Function GetLotBadData(ByVal szEquipID As String, ByVal szGroupKeyNo As String) As DataTable
        Dim szSql As String = ""

        szSql &= "SELECT LOT_NO, SUM(CAT_CNT) LOT_BAD_CNT FROM PER.L_LOT_BAD_DATA  "
        szSql &= " WHERE EQUIP_ID = ? AND WAFER_LOTNO = ? "
        szSql &= " GROUP BY LOT_NO "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szEquipID, szGroupKeyNo)
        If IsNothing(dt) Then
            GetLotBadData = New DataTable
        Else
            GetLotBadData = dt
        End If
    End Function

    Public Function GetLotBadData2(ByVal szEquipID As String, ByVal szLotKeyNo As String) As DataTable
        Dim szSql As String = ""

        szSql &= "SELECT LOT_NO, SUM(CAT_CNT) LOT_BAD_CNT FROM PER.L_LOT_BAD_DATA  "
        szSql &= " WHERE EQUIP_ID = ? AND LOT_NO = ? "
        szSql &= " GROUP BY LOT_NO "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szEquipID, szLotKeyNo)
        If IsNothing(dt) Then
            GetLotBadData2 = New DataTable
        Else
            GetLotBadData2 = dt
        End If
    End Function

    Public Function GetLotDefect(ByVal szEquipID As String, ByVal szGroupKeyNo As String) As DataTable
        Dim szSql As String = ""

        szSql &= "SELECT CAT_CD, CAT_NAME, CAT_CNT, EQUIP_NO, KOUTEI_CD "
        szSql &= "FROM TRA.L_LOT_DEFECT "
        szSql &= "WHERE EQUIP_ID = ? AND LOT_NO = ? "
        szSql &= "ORDER BY CAT_CD "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szEquipID, szGroupKeyNo)
        If IsNothing(dt) Then
            GetLotDefect = New DataTable
        Else
            GetLotDefect = dt
        End If
    End Function
    Public Function DeleteLotBadData(ByVal szGroupKeyNo As String, ByVal szEquipID As String) As Integer

        Dim szSqlDel As String = ""
        szSqlDel &= "DELETE FROM PER.L_LOT_BAD_DATA "
        szSqlDel &= "  WHERE WAFER_LOTNO = ? "
        szSqlDel &= "AND EQUIP_ID = ? "

        DBacs.ExecuteUpdateSql(szSqlDel, -1, szGroupKeyNo, szEquipID)

        DeleteLotBadData = 0
    End Function

    Public Function DeleteLotBadData2(ByVal szLotKeyNo As String, ByVal szEquipID As String) As Integer

        Dim szSqlDel As String = ""
        szSqlDel &= "DELETE FROM PER.L_LOT_BAD_DATA "
        szSqlDel &= "  WHERE LOT_NO = ? "
        szSqlDel &= "AND EQUIP_ID = ? "

        DBacs.ExecuteUpdateSql(szSqlDel, -1, szLotKeyNo, szEquipID)

        DeleteLotBadData2 = 0
    End Function

    Public Function InsertLotBadData(ByVal szEquipId As String, ByVal szEquipno As String, ByVal szGroupKeyNo As String, ByVal szLotNo As String,
                                     ByVal szCatCd As String, ByVal szCatName As String, ByVal szCatCnt As String, ByVal szKouteiCd As String) As Integer

        Dim szSql As String = ""
        szSql &= "INSERT INTO PER.L_LOT_BAD_DATA( "
        szSql &= "  EQUIP_ID, EQUIP_NO, WAFER_LOTNO, LOT_NO, CAT_CD, CAT_NAME, CAT_CNT, KOUTEI_CD "
        szSql &= ") "
        szSql &= "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?) "

        DBacs.ExecuteUpdateSql(szSql, -1, szEquipId, szEquipno, szGroupKeyNo, szLotNo, szCatCd, szCatName, szCatCnt, szKouteiCd)

        InsertLotBadData = 0
    End Function
    Public Function SaveLotBadData(ByVal szLot As String, ByVal szEquipId As String, ByVal szEquipno As String, ByVal szKouteiCd As String, ByVal szPreProcLotType As String) As Integer
        Dim szSql As String = ""
        '戻り値(データテーブル)格納用
        Dim szLotDefect As DataTable = GetLotDefect(szEquipId, szLot)
        Dim szLotSize As DataTable = GetLotSizeData(szEquipId, szLot, szPreProcLotType, "1")
        '戻り値(数値)格納用
        Dim iRet As Integer
        Dim iLotDefectCnt As Integer
        Dim iLotSizeCnt As Integer
        Dim iLoop1, iLoop2 As Integer
        Dim iLotSizeRes, iBadCountRes, iBadCount, iLotcount As Integer

        If szPreProcLotType = "0" Then
            iRet = DeleteLotBadData(szLot, szEquipId)
        Else
            iRet = DeleteLotBadData2(szLot, szEquipId)
        End If

        iLotDefectCnt = szLotDefect.Rows.Count
        iLotSizeCnt = szLotSize.Rows.Count
        iLotSizeRes = 0
        iBadCountRes = 0
        iBadCount = 0
        iLotcount = 0

        If iLotDefectCnt > 0 Then
            For iLoop1 = 0 To iLotDefectCnt - 1
                iBadCountRes = CInt(szLotDefect.Rows(iLoop1)("CAT_CNT").ToString)

                For iLoop2 = iLotcount To iLotSizeCnt - 1
                    If iLotSizeRes = 0 Then
                        iLotSizeRes = CInt(szLotSize.Rows(iLoop2)("LOT_SIZE").ToString)
                    End If
                    If iLotSizeRes > iBadCountRes Then
                        iBadCount = iBadCountRes
                        iLotSizeRes = iLotSizeRes - iBadCountRes
                        iBadCountRes = 0
                    Else
                        iBadCount = iLotSizeRes
                        iBadCountRes = iBadCountRes - iLotSizeRes
                        iLotSizeRes = 0
                    End If

                    'テーブル出力
                    iRet = InsertLotBadData(szEquipId, szEquipno, szLot, szLotSize.Rows(iLoop2)("LOT_NO").ToString, szLotDefect.Rows(iLoop1)("CAT_CD").ToString,
                                            szLotDefect.Rows(iLoop1)("CAT_NAME").ToString, CStr(iBadCount), szKouteiCd)

                    If iBadCountRes <= 0 Then
                        iLotcount = iLoop2
                        Exit For
                    End If
                Next
            Next
        End If


        SaveLotBadData = 0
    End Function

    '>>20180113 佐川
    Public Function GetDefectData(ByVal szLotNo As String, ByVal szEquipID As String, ByVal szProcCD As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT CAT_CD as コード, CAT_NAME as 不良内容, CAT_CNT as 数量 "
        szSql &= "  FROM PER.L_LOT_BAD_DATA "
        szSql &= "WHERE LOT_NO = '" & szLotNo & "' "
        szSql &= "AND EQUIP_ID = '" & szEquipID & "' "
        szSql &= "AND KOUTEI_CD = '" & szProcCD & "' "
        szSql &= "ORDER BY CAT_CD"

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szLotNo)
        If IsNothing(dt) Then
            GetDefectData = New DataTable
        Else
            GetDefectData = dt
        End If
    End Function
    '<<20180113 佐川

    '>>20180117 佐川
    Public Function DeleteLotBadData3(ByVal szLotKeyNo As String, ByVal szEquipID As String, ByVal lotno As String) As Integer

        Dim szSqlDel As String = ""
        szSqlDel &= "DELETE FROM PER.L_LOT_BAD_DATA "
        szSqlDel &= "  WHERE LOT_NO = ? "
        szSqlDel &= "AND EQUIP_ID = ? "
        szSqlDel &= "AND LOT_No = ? "

        DBacs.ExecuteUpdateSql(szSqlDel, -1, szLotKeyNo, szEquipID, lotno)

        DeleteLotBadData3 = 0
    End Function
    '<<20180117 佐川

End Class
